package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.annotation.Resource;

import model.Manufacturer;
import model.User;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

@Service("manufacturerDAO")
public class ManufacturerDAO {
	public static final String INSERT = "INSERT INTO MANUFACTURERS "
			+ "(ID,U_ID,NAME,DESC,COUNTRY,CITY,DISTRICT,TEL,WEBSITE) "
			+ "VALUES(?,?,?,?,?,?,?,?,?)";
	public static final String UPDATE = "UPDATE USERS "
			+ "SET ID=?,USERNAME=?,PASSWORD=?,role=? " + "WHERE ID=?";
	public static final String DELETE = "DELETE FROM USERS " + "WHERE ID=?";

	public static final String ALL_MANU = "SELECT * " + "From MANUFACTURERS ";

	public static final String READ_BY_ID = "SELECT * " + "FROM MANUFACTURERS"
			+ "WHERE U_ID = ?";

	public static final String READ_BY_USERNAME = "SELECT * " + "FROM USERS "
			+ "WHERE USERNAME = ?";

	@Resource
	private JdbcTemplate jdbcTemplate;

	public User addUser(final String id, final String username,
			final String password, final int role) throws SQLException {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(INSERT,
						Statement.RETURN_GENERATED_KEYS);
				pst.setString(1, id);
				pst.setString(2, username);
				pst.setString(3, password);
				pst.setInt(4, role);
				return pst;
			}
		};
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(preparedStatementCreator, keyHolder);
		User user = (User) getOneUser(keyHolder.getKey().intValue());
		return user;
	}

	public User getOneUser(final int id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(READ_BY_ID);
				pst.setInt(1, id);
				return pst;
			}
		};
		List<User> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new User(resultSet.getString("id"), resultSet
								.getString("username"), resultSet
								.getString("password"), resultSet
								.getInt("role"));
					}
				});
		return result.get(0);
	}

	public User getOneUser(final String username) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection
						.prepareStatement(READ_BY_USERNAME);
				pst.setString(1, username);
				return pst;
			}
		};
		List<User> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<User>() {
					@Override
					public User mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new User(resultSet.getString("id"), resultSet
								.getString("username"), resultSet
								.getString("password"), resultSet
								.getInt("role"));
					}
				});
		if (result.size() == 0) {
			return null;
		}
		return result.get(0);
	}
	
	public List<Manufacturer> getManuFactureByUId(final String u_id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection
						.prepareStatement(READ_BY_USERNAME);
				pst.setString(1, u_id);
				return pst;
			}
		};
		List<Manufacturer> result = jdbcTemplate.query(preparedStatementCreator,
				new RowMapper<Manufacturer>() {
					@Override
					public Manufacturer mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new Manufacturer(resultSet.getString("id"), resultSet.getString("u_id"), resultSet.getString("name"), resultSet.getString("description"),resultSet.getString("address") , resultSet.getString("tel"), resultSet.getString("website"));
					}
				});
		if (result.size() == 0) {
			return null;
		}
		return result;
	}

	public void deleteProduct(final String id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(DELETE,
						Statement.RETURN_GENERATED_KEYS);
				pst.setString(1, id);
				return pst;
			}
		};
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(preparedStatementCreator, keyHolder);
	}

	public void updateProduct(final String id) {
		PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement pst = connection.prepareStatement(UPDATE,
						Statement.RETURN_GENERATED_KEYS);
				pst.setString(1, id);
				return pst;
			}
		};
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(preparedStatementCreator, keyHolder);
	}

	public List<Manufacturer> getAllManu() {
		List<Manufacturer> result = jdbcTemplate.query(ALL_MANU,
				new RowMapper<Manufacturer>() {
					@Override
					public Manufacturer mapRow(ResultSet resultSet, int i)
							throws SQLException {
						return new Manufacturer(resultSet.getString("id"),
								resultSet.getString("u_id"), resultSet
										.getString("name"), resultSet
										.getString("description"), resultSet
										.getString("address"), resultSet
										.getString("tel"), resultSet
										.getString("website"));
					}
				});
		if (result.isEmpty())
			return null;
		return result;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
}
